package com.corejsf;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.enterprise.context.ApplicationScoped;
import javax.inject.Named;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

@ApplicationScoped
@Named("dao")
public class Dao {
	
	private Connection conn;
	private Map<String, User> users = new HashMap<String, User>();
	private DataSource ds;
	
	
	public Connection createConnection() {
		Connection conn = null;
		try {
//			String userName = "root";
//			String password = "pinkie";
//			String url = "jdbc:mysql://localhost/mylittleforum";
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
//			conn = DriverManager.getConnection(url, userName, password);
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("jdbc/mysqlforum");
			conn = ds.getConnection();
		} catch(Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		return conn;
	}
	
	public Dao() {
		conn = createConnection();
	}
	
	public void createPost(Post po) {
		PreparedStatement p;
		try {
			p = conn.prepareStatement("INSERT INTO post (title, content, timePosted, userId, threadId) VALUES (?,?,?,?,?);", PreparedStatement.RETURN_GENERATED_KEYS);
			p.setString(1, po.getTitle());
			p.setString(2, po.getContent());
			p.setDate(3, new java.sql.Date(po.getTimeStamp().getTime()));
			p.setInt(4, po.getAuthor().getId());
			p.setInt(5, po.getThreadId());
			p.execute();
			ResultSet key = p.getGeneratedKeys();
			key.next();
			po.setId(key.getInt(1));
			p.close();
		} catch(SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}
	
	public void createThread(Thread thr) {
		PreparedStatement p;
		try {
			p = conn.prepareStatement("INSERT INTO thread(catId) VALUES (?);", PreparedStatement.RETURN_GENERATED_KEYS);
			p.setInt(1, thr.getCat().getId());
			p.execute();
			ResultSet key = p.getGeneratedKeys();
			key.next();
			thr.setId(key.getInt(1));
			p.close();
		} catch(SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}
	
	public void createUser(User newUser) {
		PreparedStatement p;
		try {
			p = conn.prepareStatement("INSERT INTO user(nick, pass, userLevel) VALUES (?,?,?);", PreparedStatement.RETURN_GENERATED_KEYS);
			p.setString(1, newUser.getNick());
			p.setString(2, newUser.getPass());
			p.setInt(3, newUser.getLevel().ordinal());
			p.execute();
			ResultSet key = p.getGeneratedKeys();
			key.next();
			newUser.setId(key.getInt(1));
			p.close();
		} catch(SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}
	
	public void createCategory(Category cat) {
		PreparedStatement p;
		try {
			p = conn.prepareStatement("INSERT INTO category(name) VALUES (?);", PreparedStatement.RETURN_GENERATED_KEYS);
			p.setString(1, cat.getName());
			p.execute();
			ResultSet key = p.getGeneratedKeys();
			key.next();
			cat.setId(key.getInt(1));
			p.close();
		} catch(SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
	}

	public List<Post> getPosts(Thread thread) {
		List<Post> result = new ArrayList<Post>();
		try {
			PreparedStatement p = conn.prepareStatement("SELECT p.id AS postId, p.title AS postTitle, p.content, p.timePosted, u.id, u.nick, u.userLevel, p.threadId AS threadId FROM post p INNER JOIN user u ON p.userId = u.id WHERE p.threadId = ?;");
			p.setInt(1, thread.getId());
			p.execute();
			ResultSet rs = p.getResultSet();
			while(rs.next()) {
				Post po = new Post();
				
				po.setAuthor(getUser(rs.getString("nick")));
				po.setTitle(rs.getString("postTitle"));
				po.setContent(rs.getString("content"));
				po.setId(rs.getInt("postId"));
				po.setTimeStamp(rs.getDate("timePosted"));
				po.setThreadId(rs.getInt("threadId"));
				
				result.add(po);
			}
			p.close();
		} catch(SQLException e) {
			throw new RuntimeException(e.getMessage());
		}
		return result;
	}

	private User getUser(String name) {
		if(users.containsKey(name)) {
			return users.get(name);
		} else {
			getUsers();
			return users.get(name);
		}
	}

	public List<Thread> getThreads(Category cat) {
		List<Thread> result = new ArrayList<Thread>();
		PreparedStatement p;
		try {
			p = conn.prepareStatement("SELECT t.id, p.title, MIN(p.timePosted) FROM thread t INNER JOIN post p ON t.id = p.threadId WHERE t.catId = ? GROUP BY p.threadId ASC");
			p.setInt(1, cat.getId());
			p.execute();
			ResultSet rs = p.getResultSet();
			while(rs.next()) {
				Thread c = new Thread();
				c.setId(rs.getInt("id"));
				c.setTitle(rs.getString("title"));
				result.add(c);
			}
			p.close();
		} catch(SQLException e) {
			throw new RuntimeException("SQLERROR" + e.getMessage());
		}
		return result;
	}

	public List<Category> getCategories() {
		List<Category> result = new ArrayList<Category>();
		try {
			Statement s = conn.createStatement();
			s.executeQuery("SELECT name, id FROM category;");
			ResultSet rs = s.getResultSet();
			while(rs.next()) {
				Category c = new Category();
				c.setName(rs.getString("name"));
				c.setId(rs.getInt("id"));
				result.add(c);
			}
			s.close();
		} catch(SQLException e) {
			throw new RuntimeException("SQLERROR" + e.getMessage());
		}
		return result;
	}

	public List<User> getUsers() {
		List<User> result = new ArrayList<User>();
		try {
			PreparedStatement p = conn.prepareStatement("SELECT nick, pass, userLevel, id FROM user;");
			p.execute();
			ResultSet rs = p.getResultSet();
			while(rs.next()) {
				User u = new User();
				u.setNick(rs.getString("nick"));
				u.setPass(rs.getString("pass"));
				u.setId(rs.getInt("id"));
				u.setLevel(UserLevel.valueOf(rs.getString("userLevel")));
				users.put(u.getNick(), u);
				result.add(u);
			}
			p.close();
		} catch(SQLException se) {
			throw new RuntimeException("SQLERROR" + se.getMessage());
			
		} catch(NullPointerException ne) {
			if(ds == null) {
				throw new RuntimeException("DataSource == null");
			}
			if(conn == null) {
				throw new RuntimeException("Conns == null");
			}
		}
		return result;
	}

	public int gPostCount(User u) {
		// TODO NYI
		return 0;
	}
	
	public void deleteUser(User u) {
		// TODO NYI
	}
	
	public void deleteThread(Thread t) {
		PreparedStatement p;
		try {
			p = conn.prepareStatement("DELETE FROM thread WHERE id = ?");
			p.setInt(1, t.getId());
			p.execute();
			p.close();
		} catch(SQLException sqe) {
			throw new RuntimeException("SQLERROR: " + sqe.getMessage());
		}
	}
	
	public void deletePost(Post p) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("DELETE FROM post WHERE id = ?");
			ps.setInt(1, p.getId());
			ps.execute();
			ps.close();
		} catch(SQLException sqe) {
			throw new RuntimeException("SQLERROR: " + sqe.getMessage());
		}
	}
	
	public void deleteCategory(Category c) {
		PreparedStatement p;
		try {
			p = conn.prepareStatement("DELETE FROM category WHERE id = ?");
			p.setInt(1, c.getId());
			p.execute();
			p.close();
		} catch(SQLException sqe) {
			throw new RuntimeException("SQLERROR: " + sqe.getMessage());
		}
	}

}
